*** Auctions Transform Data ***
* produces relevant buckets of aggregation
* produces time series plotsseries plots that are not in the paper together with much more detailed analysis than found in the paper
clear

* Upload File Used
global replication `c(pwd)'
global data "$replication\Data"
global inter "$replication\Dta - Intermediate"
global final "$replication\Dta - Final"
global graphs "$replication\Graphs"
import delimited "$data\Subastas.Summary.0216.ToStata.csv"

* Describe data
* desc 

* Set Color Scheme - INSTALL IF NOT READY
ssc install unique, 
ssc install blindschemes
set scheme plottig

* Raw Variable List - Data Dictionary 	** Galo, please check if names make sense	| Saki's translation to english (tranformed to and labeled as)
* isin            					str12   %12s      ISIN                            	 isin                left as string "ISIN"
* nombredelaemision 				str51   %51s      NOMBRE DE LA EMISION               bondclass 		     integer		"Bond Class"
* cupón           					str7    %9s       CUPÓN								 coupon				 real			"Coupon"
* fechaemisióndelprimertramo 		str10   %10s      FECHA EMISIÓN DEL PRIMER TRAMO 	 vintage_date        date			"Vintage"
* fechadeamortización 				str10   %10s      FECHA DE AMORTIZACIÓN				 expiration_date     date			"Expiration"
* numerodetramo   					byte    %8.0g     NUMERO DE TRAMO					 tranchnum           integer   	    "# Tranch"
* numerodevuelta  					byte    %8.0g     NUMERO DE VUELTA					 roundnum            integer 		"# Round"
* fechadedesembolsodelasubasta 		str10   %10s      FECHA DE DESEMBOLSO DE LA SUBASTA  issuance            date			"Issuance Date"
* nominalapeticionescompetitivas 	str18   %18s   	  NOMINAL PETICIONES COMPETITIVAS    totcompbid			 real			"Total Competitive Nominal"
* nominalapeticionesnocompetitivas 	str18   %18s 	  NOMINAL PETICIONES NO COMPETITIVAS totnocompbid        real           "Total Non-Compete Nominal"
* nominaladjudicado 				str18   %18s      NOMINAL ADJUDICADO				 totalot  			 real			"Total Allocated"
* nominalemitido  					str18   %18s      NOMINAL EMITIDO				     totissued			 real			"Total Issued"
* nominaladjudicadoalmarginal 		str18   %18s      NOMINAL ADJUDICADO AL MARGINAL     marginalat			 real			"Marginal Assigned"
* preciomarginal  					str7    %9s       PRECIO MARGINAL					 marginprice		 real			"Marginal Price"
* tipomarginal    					str7    %9s       TIPO MARGINAL						 marginrate			 real			"Marginal Rate"
* preciomediomeponderado 			str7    %9s       PRECIO MEDIO MEDIO PONDERADO       wavprice			 real			"Weighted Av. Price"
* tipomedioponderado 				str7    %9s       TIPO MEDIO PONDERADO               wavrate			 real			"Weighted Av. Rate"
* primerprecionoaceptado 			str7    %9s       PRIMER PRECIO NO ACEPTADO          marginpriceup       real 			"Marginal Price (above cut)"
* nominalsolicitadoaeseprecio 		long    %12.0g    NOMINAL SOLICITADO A ESE PRECIO    priceatmargin    	 real   		"Price at Margin"


* Number of distinct values
replace isin=strlower(isin)
unique isin
by isin, sort: gen new_isin = _n == 1
label var new_isin "Indicator of New Vintage"
gen isin_indic = sum(new_isin)
label var isin_indic "Cohort of Vintage"
* ssc inst egenmore
bysort isin: gen isin_freq = _N
label var isin_freq "Count of Isin #"
* -> approximately 268 in latest count

* Transforming Date Strings into Strings
gen vintage_date=date(fechaemisióndelprimertramo, "MDY")
format vintage_date %td
label var vintage_date "Vintage"
hist vintage_date

gen expiration_date=date(fechadeamortización, "MDY")
format expiration_date %td
label var expiration_date "Expiration"
gen issuance_date=date(fechadedesembolsodelasubasta,"MDY")
format issuance_date %td
label var issuance_date "Issuance"
gen iss_yr=year(issuance_date)
label var iss_yr "Year of Issuance"
gen iss_mn=month(issuance_date)
label var iss_mn "Month of Issuance"
gen iss_dy=day(issuance_date)
label var iss_dy "Day of Issuance"
gen vin_yr=year(issuance_date)
label var vin_yr "Year of Vintage"
gen vin_mn=month(issuance_date)
label var vin_mn "Month of Vintage"
gen vin_dy=day(issuance_date)
label var vin_dy "Day of Vintage"
gen dyofwk=dow(issuance_date)
label var dyofwk "Day of the Week"
label define dyofwk 1 "Monday", modify
label define dyofwk 2 "Tuesday", modify
label define dyofwk 3 "Wednesday", modify
label define dyofwk 4 "Thursday", modify
label define dyofwk 5 "Friday", modify
g iss_my=mofd(issuance_date)
label var iss_my "Issuance Month Year"
* tab iss_my
format iss_my %tm

* Drop Variables
display as text "simple summary = " 
list fechaemisióndelprimertramo vintage_date if (_n<5) 

* list issuance_date dyofwk

* Use this to check if dates coincide


* Type of Issuance * Switch from string to categorical
encode nombredelaemision, gen(bondclass) 			
label var bondclass "Bond Class"
label list bondclass
recode bondclass ( 2 4 = 3 )
recode bondclass ( 5 = 4 ) 
recode bondclass ( 1 = 5 ) 
recode bondclass ( 6 = 1 ) 
recode bondclass ( 7 = 2 ) 
recode bondclass (8 = 6)
label define bondclass 1 "LETRAS TESORO (3 Month)", modify
label define bondclass 2 "LETRAS TESORO (6 Month)", modify
label define bondclass 3 "LETRAS TESORO  (1 Year)", modify
label define bondclass 4 "LETRAS TESORO (18 Month)", modify
label define bondclass 5 "BONOS ESTADO", modify
label define bondclass 6 "OBLIGACIONES ESTADO", modify
label define bondclass 7 "", modify
label define bondclass 8 "", modify
label list bondclass

* Letras issued tuesdays, bonds Fridays
drop nombredelaemision fechaemisióndelprimertramo fechadeamortización fechadedesembolsodelasubasta

* Histogram
histogram bondclass, discrete fraction addlabel xlabel(#6, labels angle(vertical) format(%12s) valuelabel ticks tposition(inside) grid) legend(on) 
* * graph export  "C:\Users\saki\Dropbox (CBS)\Debt.Management\data\subastas.spain\Auction Figures\AuctionFrequency.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> in model, should be uniform
* graph export  "Auction Figures\AuctionFrequency.ps", as(ps) logo(off) or(landscape) tm(1) replace
hist dyofwk, by(bondclass) xlabel(, labels angle(vertical) grid) 
* graph export  "Auction Figures\DayOfWeek.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> Letras issued on Tuesdays..." 


* Transforming Strings to Reals
gen coupon = real(cupón)
label var coupon "Coupon"
drop cupón
gen totcompbid   = real(nominalpeticionescompetitivas) 	 			 
label var totcompbid "Total Competitive Nominal"
drop nominalpeticionescompetitivas
gen totnocompbid = real(nominalpeticionesnocompetitivas)         
label var totnocompbid "Total Non-Compete Nominal"
drop nominalpeticionesnocompetitivas
gen totalot      = real(nominaladjudicado) 				   			 
label var totalot "Total Allocated"
drop nominaladjudicado
gen totissued    = real(nominalemitido)  				 			 
label var totissued "Total Issued"
drop nominalemitido
gen marginalot   = real(nominaladjudicadoalmarginal) 	 			 
label var marginalot "Marginal Assigned"
drop nominaladjudicadoalmarginal
gen marginprice  = real(preciomarginal)  				 		 
label var marginprice "Marginal Price"
drop preciomarginal
gen marginrate   = real(tipomarginal)    				 			 
label var marginrate "Marginal Rate"
drop tipomarginal
gen wavprice     = real(preciomediomedioponderado) 			 			 
label var wavprice "Weighted Av. Price"
drop preciomediomedioponderado
gen wavrate      = real(tipomedioponderado) 				 			 
label var wavrate "Weighted Av. Rate"
drop tipomedioponderado
gen marginpriceup= real(primerprecionoaceptado) 			        
label var marginpriceup "Marginal Price (above cut)"
drop primerprecionoaceptado
rename nominalsolicitadoaeseprecio priceatmargin 	     	 
label var priceatmargin "Price at Margin" 
gen dayspastvintage=issuance_date-vintage_date
label var dayspastvintage "Days Since First Vintage"

* Integers
gen tranchnum=int(numerodetramo) 
label var tranchnum "# Tranch"
gen  roundnum=int(numerodevuelta) 
label var roundnum "# Round"
drop numerodetramo numerodevuelta

* Creating New Variables = prefix org for original
gen days2mat=expiration_date-issuance_date
label var days2mat "Days to Maturity"
gen orgdaysmat=expiration_date-vintage_date
label var orgdaysmat "Original Maturity (days)"
gen mon2mat=days2mat/30
label var mon2mat "Months to Maturity"
gen orgmon2mat=orgdaysmat/30
label var orgmon2mat "Original Maturity (months)"
gen y2mat=days2mat/365
label var y2mat "Years to Maturity"
gen orgy2mat=orgdaysmat/365
label var orgy2mat "Original Maturity (years)"
gen mar_wavprice=marginprice/wavprice
label var mar_wavprice "Marginal to Weighted Price"
gen mar_wavrate=marginrate-wavrate
label var mar_wavrate "Marginal minus Weighted Rate"
gen totbid=totcompbid+totnocompbid
label var totbid "Total Bids"
gen coverage=totissued/totbid
label var coverage "Issuance over Bids"
gen mar_totalot=marginalot/totalot
label var mar_totalot "Allocation to Marginal (over Total)"
* -> Question for Galo: marginal alotment is cummulative, or specific to the one single marginal bid...
gen rmon2mat=round(mon2mat)
label var rmon2mat "Months to Maturity (rounded)"

* Checks of interpretation
scatter dayspastvintage tranchnum if(bondclass<5), by(bondclass)
* graph export  "Auction Figures\daysafterfirst_letra.ps", as(ps) logo(off) or(landscape) tm(1) replace
scatter dayspastvintage tranchnum if(bondclass==5), by(bondclass)
* graph export  "Auction Figures\daysafterfirst_bono.ps", as(ps) logo(off) or(landscape) tm(1) replace
scatter dayspastvintage tranchnum if(bondclass==6), by(bondclass)
* graph export  "Auction Figures\daysafterfirst_obli.ps", as(ps) logo(off) or(landscape) tm(1) replace
* some pattern in bonos estado and obligaciones. Letras seem a bit of confusing


* Now constructing time series of average maturity
* if not installed, ssc inst _gwtmean, replace
egen matofmonth=mean(rmon2mat), by(iss_my)
label var matofmonth "Average Maturity of Issuances that Month"
egen wmatofmonth=wtmean(rmon2mat), weight(totalot) by(iss_my)
label var wmatofmonth "Maturity of Month (Value Weighted)"
egen matofyr=mean(rmon2mat), by(iss_yr)
label var matofyr "Average Maturity of Issuances that Year"
egen wmatofyr=wtmean(rmon2mat), weight(totalot) by(iss_yr)
label var wmatofyr "Maturity of Year (Value Weighted)"

* egen wmatofmonth=mean(rmon2mat) [aweight=totalot], over(iss_my)
* label var wmatofmonth "Value Weigheted Average Maturity of Issuances that Month"


* Auction Identifier
by vintage_date, sort: gen auction_bnpid = _n == 1
replace auction_bnpid = sum(auction_bnpid)
label var auction_bnpid "Auction Identifier"
* we have a total of 263 auctions

* Relabeling and Reorganizing by security
encode isin, gen(sec_bnpid)
sort sec_bnpid issuance_date
order sec_bnpid, first
label var sec_bnpid "Security (BNP id)"

* Cummulative
egen sum_issuance=sum(totalot/wavprice), by(sec_bnpid)
label var sum_issuance "Security Total Face Value (all issuances)"
bysort sec_bnpid : gen cum_iss = sum(totalot/wavprice)
label var cum_iss "Security Total Face Value (time)"


*******************************************************************************************
* Main Descriptive Plots to Understand the Data
*******************************************************************************************
* [1.a] Original Maturity structure
hist orgmon2mat if(bondclass<5) , by(bondclass)
* graph export  "Auction Figures\orgmat_letra.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> most 3 month letras, were an isin issued at year or 18 month frequency
hist orgy2mat if(bondclass==5), bin(18)
* graph export  "Auction Figures\orgmat_bono.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> for obligaciones, issuances concentrate in +>60 months (5 years) and (3 years)
hist orgy2mat if(bondclass==6), bin(18)
* graph export  "Auction Figures\orgmat_obli.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> for bonos, issuances concentrate in:
* 10, 15, 30, and 50
* -> MAIN MESSAGE: original maturity is mostly meaningless, as the bond is opened again in shorter maturities...

* [1.b] Pending Maturity
hist mon2mat if(bondclass<5) , by(bondclass)
* graph export  "Auction Figures\mat_letras.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> the label corresponds to the issuance (auction), not the vintage: hence, the pattern makes a lot of sence. 
* -> 1 yr letra is issued a bit in 9. Seems to be some reclassification issue
hist mon2mat if(bondclass==1) , by(bondclass)
* graph export  "Auction Figures\mat_bono.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> for obligaciones, pattern looks much better...this is what I expected above...
hist y2mat if(bondclass==6) , by(bondclass)
* -> again, a reasonable pattern...
* graph export  "Auction Figures\mat_obli.ps", as(ps) logo(off) or(landscape) tm(1) replace


* [1.c] Pending Maturity - Rounded
hist rmon2mat if(bondclass<5), fraction by(bondclass)
hist rmon2mat if(bondclass==5), fraction by(bondclass)
hist rmon2mat if(bondclass==6), fraction by(bondclass)
hist rmon2mat if(bondclass==6&iss_yr==2016), fraction by(bondclass)
hist rmon2mat if(bondclass==5&iss_yr==2016), fraction by(bondclass)
hist rmon2mat if(bondclass==6), by(iss_yr)
* -> rounding doesn't fully resolve the issue...

* [1.d] Maturity Structure, by date
scatter rmon2mat iss_my if(bondclass==5)
* graph export  "Auction Figures\ts_mat@iss_bono.ps", as(ps) logo(off) or(landscape) tm(1) replace
scatter y2mat iss_my if(bondclass==6) 
* graph export  "Auction Figures\ts_mat@iss_obli.ps", as(ps) logo(off) or(landscape) tm(1) replace
scatter rmon2mat iss_my if(bondclass<5)
scatter rmon2mat iss_my if(bondclass==5), by(isin)
scatter rmon2mat iss_my if(bondclass==6), by(isin)
scatter rmon2mat iss_my if(bondclass==5), by(isin)
scatter rmon2mat iss_my if(bondclass==6&iss_yr>2010&iss_yr<=2012), by(isin)
* -> an ISIN opens once or twice a year. An you continue to issue in it for a while...
* -----> pretty much the same as letras, but less predictable...
* -> Letras have a consistent maturity pattern. Although not perfect. Some can be over 15 days late, and still be call 3 months, whereas 4 month if rounded.
* -> this is fine.
* -> for Obligaciones, pattern seemed more consistent in past. Last chart shows shortening of issuances
* -> bonos, same thing. Seems that obligaciones have a less usual structure. There seems to be consistent demand by thi bonds, 
* -> and has to do with the predictability in the supply. I suspect this has to do with role in banks, which helps coordination.
* -> would have wanted to create discrete category, but this data suggests not a good idea.
* -> it's amazing how once a bond is opened, it continues to open
* -> it's as if, by the end of the year, they want bankers to have a simple portfolio-> perhas for regulatory reasons?

* [2] Coupon Structure
scatter coupon issuance_date, by(bondclass) xlabel(,angle(vertical) grid)
scatter coupon mon2mat, by(bondclass)
scatter coupon orgmon2mat, by(bondclass)
* -> clearly, letras are zero coupon bonds
* -> in the time series, you can see, the decline in rates

* [3] Structure of Bids Types
* Bids by type of bid for issuance date
scatter totcompbid totnocompbid issuance_date
* -> you see that only for a few days, you see a non comp bid
scatter totcompbid totnocompbid issuance_date, by(bondclass) xlabel(,angle(vertical) grid)
* -> action only among obligaciones estado. 
* -> suggests distinction can be ignored.
* -> to my surprise, use of letras is important...a lot is issued
scatter mar_totalot issuance_date, by(bondclass) xlabel(,angle(vertical) grid)
* -> no clear pattern on the marginal guy...

* [4] Structure of Bids Types
scatter coverage issuance_date, by(bondclass) xlabel(,angle(vertical) grid)
* -> bond issuance consistently well covered although more coverage pressure during sovereing crisis

* [5] Structure of Bid Prices and Rates
scatter mar_wavprice issuance_date if(bondclass==1), by(bondclass) xlabel(,angle(vertical) grid)
* -> stress in crisis, always close to 1 in tranquil times
scatter mar_wavprice issuance_date if(bondclass<5), by(bondclass) xlabel(,angle(vertical) grid)
* graph export  "Auction Figures\mar2weight_letras.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> stress in crisis, always close to 1, again clearly a pattern for the crisis
scatter mar_wavprice issuance_date if(bondclass==6) , by(bondclass) xlabel(,angle(vertical) grid)
* graph export  "Auction Figures\mar2weight_obli.ps", as(ps) logo(off) or(landscape) tm(1) replace
* -> bond issuance consistently well covered although more coverage pressure during sovereing crisis
* -> basis points are rather small...on the order of 10. Show very little dispersion...we need to compare to the secondary market
scatter mar_wavrate issuance_date if(bondclass==1), by(bondclass) xlabel(,angle(vertical) grid)
* ! alert-> not sure what this is
scatter mar_wavrate issuance_date if(bondclass<5), by(bondclass) xlabel(,angle(vertical) grid)
* ! -> weird outliers in 16, seems a drop in quality in registration of numbers.
scatter mar_wavrate issuance_date if(bondclass==6) , by(bondclass) xlabel(,angle(vertical) grid)
* ! -> again, weird pattern in 16. Not sure what these rates are anyway
* ! -> alert: only up to 2018...

* [6] Price vs. Quantity by Class and Year, 
scatter mar_wavprice totalot if(bondclass==1), by(iss_yr)
scatter mar_wavprice totalot if(bondclass>1&bondclass<6), by(iss_yr)
scatter mar_wavprice totalot if(bondclass==6), by(iss_yr)
twoway (lfitci mar_wavprice totalot if(bondclass==1&iss_yr~=2012)) (scatter mar_wavprice totalot if(bondclass==1&iss_yr~=2012))
* ! -> most action, in particular around 2012...a clear slope for all bonds. but, of course, causality may be an issue...
* ! ->crisis may lead to more dispersion amongst bidders, and larger auctions
* ! -> last plot is fitted rersion

* [7] time series 
scatter totalot issuance_date, by(bondclass) xlabel(,angle(vertical) grid)
* tough to see a pattern...however, if ronda=1, you see a pattern, especially among letras tesoro, a bit with bono...
scatter totalot issuance_date if(roundnum==1), by(bondclass) xlabel(,angle(vertical) grid)
* ! -> here you see the pattern..
scatter totalot issuance_date if(iss_yr==2012&roundnum==1), by(bondclass) xlabel(,angle(vertical) grid)

* [7.b] Issuance by maturity and size
scatter rmon2mat iss_my [w=totalot] if(bondclass==1), msymbol(circle_hollow)
scatter rmon2mat iss_my [w=totalot] if(bondclass==2), msymbol(circle_hollow)
scatter rmon2mat iss_my [w=totalot] if(bondclass<5), msymbol(circle_hollow) by(bondclass)
scatter rmon2mat iss_my [w=totalot] if(bondclass==5&isin_freq>20), msymbol(circle_hollow) by(bondclass)
scatter rmon2mat iss_my [w=totalot] if(bondclass==6&isin_freq>20), msymbol(circle_hollow) by(bondclass)
* -> not that issuance size of first batch is always largest

* [8] New Issuance
scatter new_isin iss_my if(bondclass==5)
scatter totalot iss_my
scatter totalot iss_my
hist iss_my
scatter matofmont iss_my
scatter wmatofmont iss_my
twoway (scatter matofyr iss_my) (scatter wmatofyr iss_my)

* [9] Comparing new with old maturity
twoway (scatter y2mat iss_my if(bondclass==5&new_isin==0)) (scatter y2mat iss_my if(bondclass==5&new_isin==1))
* -> suspicious of an error, counting error...

* [10] 
*/

* Saving File
* save "$inter\AuctionsSummaryStats.dta", replace

rename issuance_date dates
save "$inter\AuctionsReady4Merge.dta", replace
